﻿using System;
using System.Data;

namespace AssettDatabase.DAL
{
    class Customer
    {
        private string sqltext = "";

        public bool Add(string Customer_Name, string Customer_Phone, string Customer_ABN, string Customer_Mobile, string Primary_Contact_FN,
                        string Primary_Contact_LN, string Customer_Website, string Primary_Contact_Email, string Accounts_Payable_FN,
                        string Accounts_Payable_LN, string Accounts_Payable_EML, string Customer_Notes)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext =   "INSERT INTO Customer (Customer_Name,Customer_Phone,Customer_ABN,Customer_Mobile,Primary_Contact_FN,Primary_Contact_LN,Customer_Website," +
                            "Primary_Contact_Email,Accounts_Payable_FN,Accounts_Payable_LN,Accounts_Payable_EML,Customer_Notes,Customer_Status)" +
                            "VALUES (@Customer_Name,@Customer_Phone,@Customer_ABN,@Customer_Mobile,@Primary_Contact_FN,@Primary_Contact_LN,@Customer_Website," +
                            "@Primary_Contact_Email,@Accounts_Payable_FN,@Accounts_Payable_LN,@Accounts_Payable_EML,@Customer_Notes,1);";

                mDAO.AddParameter("@Customer_Name", Customer_Name);
                mDAO.AddParameter("@Customer_Phone", Customer_Phone);
                mDAO.AddParameter("@Customer_ABN", Customer_ABN);
                mDAO.AddParameter("@Customer_Mobile", Customer_Mobile);
                mDAO.AddParameter("@Primary_Contact_FN", Primary_Contact_FN);
                mDAO.AddParameter("@Primary_Contact_LN", Primary_Contact_LN);
                mDAO.AddParameter("@Customer_Website", Customer_Website);
                mDAO.AddParameter("@Primary_Contact_Email", Primary_Contact_Email);
                mDAO.AddParameter("@Accounts_Payable_FN", Accounts_Payable_FN);
                mDAO.AddParameter("@Accounts_Payable_LN", Accounts_Payable_LN);
                mDAO.AddParameter("@Accounts_Payable_EML", Accounts_Payable_EML);
                mDAO.AddParameter("@Customer_Notes", Customer_Notes);
                
                mDAO.ExecuteNonQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }

        public bool Update(string Customer_Name, string Customer_Phone, string Customer_ABN, string Customer_Mobile, string Primary_Contact_FN,
                        string Primary_Contact_LN, string Customer_Website, string Primary_Contact_Email, string Accounts_Payable_FN,
                        string Accounts_Payable_LN, string Accounts_Payable_EML, string Customer_Notes, string Customer_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext =   "UPDATE Customer SET Customer_Name=@Customer_Name," +
                            "Customer_Phone=@Customer_Phone," +
                            "Customer_ABN=@Customer_ABN," +
                            "Customer_Mobile=@Customer_Mobile," +
                            "Primary_Contact_FN=@Primary_Contact_FN," +
                            "Primary_Contact_LN=@Primary_Contact_LN," +
                            "Customer_Website=@Customer_Website," +
                            "Primary_Contact_Email=@Primary_Contact_Email," +
                            "Accounts_Payable_FN=@Accounts_Payable_FN," +
                            "Accounts_Payable_LN=@Accounts_Payable_LN," + 
                            "Accounts_Payable_EML=@Accounts_Payable_EML," +
                            "Customer_Notes=@Customer_Notes " +
                            "WHERE Customer_ID=@Customer_ID";

                mDAO.AddParameter("@Customer_Name", Customer_Name);
                mDAO.AddParameter("@Customer_Phone", Customer_Phone);
                mDAO.AddParameter("@Customer_ABN", Customer_ABN);
                mDAO.AddParameter("@Customer_Mobile", Customer_Mobile);
                mDAO.AddParameter("@Primary_Contact_FN", Primary_Contact_FN);
                mDAO.AddParameter("@Primary_Contact_LN", Primary_Contact_LN);
                mDAO.AddParameter("@Customer_Website", Customer_Website);
                mDAO.AddParameter("@Primary_Contact_Email", Primary_Contact_Email);
                mDAO.AddParameter("@Accounts_Payable_FN", Accounts_Payable_FN);
                mDAO.AddParameter("@Accounts_Payable_LN", Accounts_Payable_LN);
                mDAO.AddParameter("@Accounts_Payable_EML", Accounts_Payable_EML);
                mDAO.AddParameter("@Customer_Notes", Customer_Notes);
                mDAO.AddParameter("@Customer_ID", Customer_ID);

                mDAO.ExecuteNonQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }

        public bool Del(string Customer_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "UPDATE Customer " +
                            "SET Customer_Status=0 " +
                            "WHERE Customer_ID=@Customer_ID";

                mDAO.AddParameter("@Customer_ID", Customer_ID);
                mDAO.ExecuteNonQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }

        public DataTable GetCustomers()
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT Customer_ID, Customer_Phone, Customer_ABN, Customer_Mobile, " +
                            "Primary_Contact_FN, Primary_Contact_LN, Customer_Website, Primary_Contact_Email, " +
                            "Accounts_Payable_FN, Accounts_Payable_LN, Accounts_Payable_EML, Customer_Notes, " +
                            "CASE WHEN Customer_Status = 1 THEN Customer_Name ELSE CONCAT(Customer_Name, ' (retired)') END AS Customer_Name " +
                            "FROM Customer " +
                            "ORDER BY Customer_Name";

                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetCustomerById(string Customer_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT Customer_ID, Customer_Name, Customer_Phone, Customer_ABN, Customer_Mobile, " +
                            "Primary_Contact_FN, Primary_Contact_LN, Customer_Website, Primary_Contact_Email, " +
                            "Accounts_Payable_FN, Accounts_Payable_LN, Accounts_Payable_EML, Customer_Notes " +
                            "From Customer " +
                            "WHERE Customer_ID=@Customer_ID";

                mDAO.AddParameter("@Customer_ID", Customer_ID);
                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetCustomerByName(string Customer_Name)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT Customer_ID, Customer_Name, Customer_Phone, Customer_ABN, Customer_Mobile, " +
                            "Primary_Contact_FN, Primary_Contact_LN, Customer_Website, Primary_Contact_Email, " +
                            "Accounts_Payable_FN, Accounts_Payable_LN, Accounts_Payable_EML, Customer_Notes " +
                            "From Customer " +
                            "WHERE Customer_Name=@Customer_Name";

                mDAO.AddParameter("@Customer_Name", Customer_Name);
                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
